package batch.makeFile;

import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.commons.lang.ObjectUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.*;

import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.io.*;
import java.util.Date;

import batch.common.db.ConnectionManager;

public class makeUserFileForGer
{
    final String TODAY = DateFormatUtils.format(new Date(), "yyyyMMdd");

    public makeUserFileForGer() {
        ;
    }

    public static void main(String [] args) {
        Logger.getLogger("makefile.makeUserFileForGer").info("( * ) Start.");

        makeUserFileForGer mkRus = new makeUserFileForGer();
        mkRus.startMake();

        Logger.getLogger("makefile.makeUserFileForGer").info("( * ) End.");
    }

    //Make a log file and a data file.
    public void startMake(){
        Connection   conn = null;
        Statement    stmt = null;
        ResultSet    rs   = null;
        ResultSetMetaData  rsmd = null;
        OutputStreamWriter oswFile = null;

        String fileNm1  = "/home01/sfc/batch/makeFile/data/ger_f300_raffle_"  + TODAY + ".csv";

        int cnt = 0;
        int rsCnt = 0;

        StringBuffer sbTemp = new StringBuffer();

        StringBuffer sQuery = new StringBuffer()
                             //.append("SELECT A.ID                                                               ")
                             //.append("     , REPLACE(REPLACE(REPLACE(REPLACE(A.FIRSTNAME, CHR(10), ''), CHR(13), ''), 'WAP_TEMP', ''), 'SIM_TEMP', '') FIRSTNAME  ")
                             //.append("     , REPLACE(REPLACE(REPLACE(REPLACE(A.LASTNAME, CHR(10), ''), CHR(13), ''), 'WAP_TEMP', ''), 'SIM_TEMP', '')  LASTNAME   ")
                             //.append("     , REPLACE(REPLACE(A.HP_BRAND, 'WAP_TEMP', ''), 'SIM_TEMP', '') HP_BRAND                                                        ")
                             //.append("     , REPLACE(REPLACE(A.HP_MODEL, 'WAP_TEMP', ''), 'SIM_TEMP', '') HP_MODEL                                                        ")
                             //.append("     , A.HP_NUM                                                           ")
                             //.append("  FROM G_MB_MEMBER A                                                      ")
                             //.append("     , G_MB_OPTIN B                                                       ")
                             //.append(" WHERE A.USER_NO     =  B.USER_NO                                         ")
                             //.append("   AND A.REG_COUNTRY =  'ger'                                             ")
                             //.append("   AND A.DELETED     IS NULL                                              ")
                             //.append("   AND A.HP_NUM      IS NOT NULL                                          ")
                             //.append("   AND B.CODE        =  '0113002'                                         ");

                             //.append("SELECT ID                                                                  \n")
                             //.append("     , FIRSTNAME                                                           \n")
                             //.append("     , LASTNAME                                                            \n")
                             //.append("     , GENDER                                                              \n")
                             //.append("     , BIRTH \"D.O.B\"                                                     \n")
                             //.append("     , EMAIL                                                               \n")
                             //.append("     , MARITAL \"MARITAL STATUS\"                                          \n")
                             //.append("     , OCCUPATION                                                          \n")
                             //.append("     , INCOME \"HOUSEHOLD INCOME\"                                         \n")
                             //.append("     , ADDRESS                                                             \n")
                             //.append("     , POSTCODE \"POSTAL CODE\"                                            \n")
                             //.append("     , CASE (SELECT CODE FROM G_MB_OPTIN B WHERE B.USER_NO = A.USER_NO AND B.CODE = '0113001') WHEN '0113001' THEN 'Y' ELSE 'N' END \"NEWSLETTER OPTIN\"      \n")
                             //.append("     , CASE (SELECT CODE FROM G_MB_OPTIN B WHERE B.USER_NO = A.USER_NO AND B.CODE = '0113002') WHEN '0113002' THEN 'Y' ELSE 'N' END \"SMS OPTIN\"             \n")
                             //.append("     , CASE (SELECT CODE FROM G_MB_OPTIN B WHERE B.USER_NO = A.USER_NO AND B.CODE = '0113003') WHEN '0113003' THEN 'Y' ELSE 'N' END \"MOBILE MESSAGES OPTIN\" \n")
                             //.append("     , HP_BRAND \"BRAND\"                                                  \n")
                             //.append("     , HP_MODEL \"MODEL\"                                                  \n")
                             //.append("     , HP_PROVIDER \"OPERATOR\"                                            \n")
                             //.append("     , HP_BUY_YM \"DATE OF PURCHASE\"                                      \n")
                             //.append("     , DELETED                                                             \n")
                             //.append("  FROM G_MB_MEMBER A                                                       \n")
                             //.append(" WHERE A.REG_COUNTRY='gbr'                                                 \n");


                             .append("SELECT ID                                   \n")
                             .append("     , FIRSTNAME                            \n")
                             .append("     , LASTNAME                             \n")
                             .append("     , GENDER                               \n")
                             .append("     , BIRTH                                \n")
                             .append("     , EMAIL                                \n")
                             .append("     , ADDRESS                              \n")
                             .append("     , POSTCODE                             \n")
                             .append("     , CITY                                 \n")
                             .append("     , (SELECT C.NAME                       \n")
                             .append("          FROM G_GER_CO_CODE C              \n")
                             .append("         WHERE C.CODE   = B.HP_BRAND        \n")
                             .append("           AND C.B_CODE = '01'              \n")
                             .append("           AND C.M_CODE = '03')  HP_BRAND   \n")
                             .append("     , HP_MODEL                             \n")
                             .append("     , TO_CHAR(HP_NUM) HP_NUM               \n")
                             .append("  FROM G_PMGM_ST_PRTCPAT  A                 \n")
                             .append("     , G_MB_MEMBER        B                 \n")
                             .append(" WHERE A.USER_NO = B.USER_NO                \n")
                             .append("   AND A.MST_CD  = '10607'                  \n")
                             .append("   AND A.SITE_CD = 'ger'                    \n");


        try {
            conn = ConnectionManager.getConnection();
            conn.setAutoCommit(true);
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sQuery.toString());

            oswFile = new OutputStreamWriter(new FileOutputStream(fileNm1, false), "ISO-8859-1");

            //oswFile.write(sbTemp.toString());

            while(rs.next()){
                rsmd = rs.getMetaData();
                rsCnt = rsmd.getColumnCount();

                sbTemp.setLength(0);

                for(int i=1;i<=rsCnt;i++) {
                    sbTemp.append("\"");

                    sbTemp.append(StringUtils.replace(ObjectUtils.toString(rs.getObject(i), ""), "\"", "\"\""));

                    if( i == rsCnt ){
                        sbTemp.append("\"\n");
                    }
                    else{
                        sbTemp.append("\",");
                    }
                }

                oswFile.write(sbTemp.toString());
                cnt++;

                if(cnt % 1000 == 0){
                    Logger.getLogger("makefile.makeUserFileForGer").info("[MEMBER]here we are == " + cnt);
                }
            }

            Logger.getLogger("makefile.makeUserFileForGer").info("[MEMBER]Total Count : " + cnt);

            if (rs   != null) try { rs.close(); }   catch (Exception e) { e.printStackTrace(); }
            if (oswFile != null) try { oswFile.close(); } catch (Exception e) { e.printStackTrace(); }

        }
        catch(Exception ex) {
            ex.printStackTrace();

            Logger.getLogger("makefile.makeUserFileForGer").info(ex.toString());
            StackTraceElement[] stElement = ex.getStackTrace();
            for(int p=0; p<stElement.length;p++){
                Logger.getLogger("makefile.makeUserFileForGer").info(stElement[p].toString());
            }
        }
        finally {
            if (rs   != null) try { rs.close(); }   catch (Exception e) { e.printStackTrace(); }
            if (stmt != null) try { stmt.close(); } catch (Exception e) { e.printStackTrace(); }
            if (conn != null) try { conn.close(); } catch (Exception e) { e.printStackTrace(); }
            if (oswFile != null) try { oswFile.close(); } catch (Exception e) { e.printStackTrace(); }
        }
    }
}